﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.IO;
using System.Configuration;
using System.Collections;
using System.Web;

namespace DAL
{
    public class SanPhamQuery : DataProvide
    {
        public List<SanPhamDAL> LayDanhSachSanPham()
        {            
            List<SanPhamDAL> dsSanPham = new List<SanPhamDAL>();
            SqlConnection chuoiKetNoi = null;
            try
            {
                base.Connect();
                chuoiKetNoi = connection;
                string sqlString = String.Format(@"SELECT * FROM SANPHAM WHERE TinhTrang = '"+1+"'");

                SqlDataReader dr = ExecuteReader(sqlString);                
                while (dr.Read())
                {
                    SanPhamDAL sanPham = (SanPhamDAL)GetDataRowFromDataReader(dr);
                    dsSanPham.Add(sanPham);
                }
            }/*
            catch
            {
                return null;
            }*/
            finally
            {
                if (chuoiKetNoi != null && chuoiKetNoi.State == System.Data.ConnectionState.Open)
                    chuoiKetNoi.Close();
            }
            return dsSanPham;
        }

        public List<SanPhamDAL> LayDanhSachSanPhamKhuyenMai()
        {
            List<SanPhamDAL> dsSanPham = new List<SanPhamDAL>();
            SqlConnection chuoiKetNoi = null;
            try
            {
                base.Connect();
                chuoiKetNoi = connection;
                string sqlString = String.Format(@"SELECT * FROM SANPHAM WHERE PhanTramKM > " + 0);

                SqlDataReader dr = ExecuteReader(sqlString);
                while (dr.Read())
                {
                    SanPhamDAL sanPham = (SanPhamDAL)GetDataRowFromDataReader(dr);
                    dsSanPham.Add(sanPham);
                }
            }/*
            catch
            {
                return null;
            }*/
            finally
            {
                if (chuoiKetNoi != null && chuoiKetNoi.State == System.Data.ConnectionState.Open)
                    chuoiKetNoi.Close();
            }
            return dsSanPham;
        }

        public List<SanPhamDAL> LayDanhSachSanPhamMoi()
        {
            List<SanPhamDAL> dsSanPham = new List<SanPhamDAL>();
            SqlConnection chuoiKetNoi = null;
            try
            {
                base.Connect();
                chuoiKetNoi = connection;
                string sqlString = String.Format(@"SELECT * FROM SANPHAM WHERE SanPhamMoi = 'True'" );

                SqlDataReader dr = ExecuteReader(sqlString);
                while (dr.Read())
                {
                    SanPhamDAL sanPham = (SanPhamDAL)GetDataRowFromDataReader(dr);
                    dsSanPham.Add(sanPham);
                }
            }/*
            catch
            {
                return null;
            }*/
            finally
            {
                if (chuoiKetNoi != null && chuoiKetNoi.State == System.Data.ConnectionState.Open)
                    chuoiKetNoi.Close();
            }
            return dsSanPham;
        }

        /// <summary>
        /// Thêm một sản phẩm mới vào cơ sỡ dữ liệu
        /// </summary>
        /// <param name="sp"> sản phẩm được thêm</param>
        /// <returns>true nếu thêm thành công false nếu thêm thất bại</returns>
        public bool ThemSanPhamMoi(SanPhamDAL sp)
        {
            bool ketQua = true;
            try
            {
                base.Connect();

                string sqlString = "INSERT INTO SANPHAM(MaSanPham, TenSanPham, MaNhaSanXuat, MaLoaiSanPham, HinhAnh, GiaMua, GiaBan, ThongSoKyThuat, ThongTinMoTa, SoLuongTon, TinhTrang, SanPhamMoi, PhanTramKM) VALUES(N'" +
                    sp.MaSanPham + "', N'" +
                    sp.TenSanPham + "', N'" +
                    sp.MaNhaSanXuat + "', N'" +
                    sp.MaLoaiSanPham + "', '" +
                    sp.HinhAnh + "', " +
                    sp.GiaMua + ", " +
                    sp.GiaBan + ", N'" +
                    sp.ThongSoKyThuat + "', N'" +
                    sp.ThongTinMoTa + "'," +
                    sp.SoLuongTon + ", '" +
                    sp.TinhTrang + "', '" +
                    sp.SanPhamMoi + "', " +
                    sp.PhanTramKM + ")";

                ExecuteNonQuery(sqlString);
            }/*
            catch (Exception ex)
            {
                ketQua = false;
            }*/
            finally
            {
                if (connection != null && connection.State == System.Data.ConnectionState.Open)
                    Disconnect();
            }
            return ketQua;
        }

        /// <summary>
        /// hiển thị sản phẩm theo mã loại
        /// </summary>
        /// <param name="dr">mã loại sản phẩm</param>
        /// <returns>danh sách sản phẩm theo loại</returns>
        public List<SanPhamDAL> LayDSSanPhamTheoMaLoai(string maloai)
        {
            List<SanPhamDAL> dsSanPham = new List<SanPhamDAL>();
            SqlConnection chuoiKetNoi = null;
            try
            {
                base.Connect();
                chuoiKetNoi = connection;
                string sqlString = String.Format(@"SELECT * FROM SANPHAM WHERE MaLoaiSanPham = '" +maloai+"'");

                SqlDataReader dr = ExecuteReader(sqlString);
                while (dr.Read())
                {
                    SanPhamDAL sanPham = (SanPhamDAL)GetDataRowFromDataReader(dr);
                    dsSanPham.Add(sanPham);
                }
            }/*
            catch
            {
                return null;
            }*/
            finally
            {
                if (chuoiKetNoi != null && chuoiKetNoi.State == System.Data.ConnectionState.Open)
                    chuoiKetNoi.Close();
            }
            return dsSanPham;
        }

        //ThuHien------------------------------------------------------
        //Tim san pham theo ten
        public List<SanPhamDAL> TimSPTheoTen(string ten)
        {
            List<SanPhamDAL> dsSanPham = new List<SanPhamDAL>();
            SqlConnection chuoiKetNoi = null;
            try
            {
                base.Connect();
                chuoiKetNoi = connection;
                string sqlString = String.Format(@"SELECT * FROM SANPHAM WHERE TenSanPham LIKE '%{0}%'", ten);

                SqlDataReader dr = ExecuteReader(sqlString);
                while (dr.Read())
                {
                    SanPhamDAL sanPham = (SanPhamDAL)GetDataRowFromDataReader(dr);
                    dsSanPham.Add(sanPham);
                }
            }/*
            catch
            {
                return null;
            }*/
            finally
            {
                if (chuoiKetNoi != null && chuoiKetNoi.State == System.Data.ConnectionState.Open)
                    chuoiKetNoi.Close();
            }
            return dsSanPham;
        }
        //tim sp nang cao
        public List<SanPhamDAL> TimSPNangCao(string ten, string maLoaiSP, float giaThap, float giaCao)
        {
            List<SanPhamDAL> dsSanPham = new List<SanPhamDAL>();
            SqlConnection chuoiKetNoi = null;
            //OleDbConnection chuoiKetNoi = null;
            try
            {
                base.Connect();
                chuoiKetNoi = connection;
                string sqlString = String.Format(@"SELECT * FROM SANPHAM WHERE ");
                if (ten != " ")
                    sqlString += string.Format(@"TenSanPham like '%{0}%'", ten);
                else
                    sqlString += string.Format(@" TenSanPham like '% %'");
                if (maLoaiSP != " ")
                    sqlString += string.Format(@" and  MaLoaiSanPham = '" + maLoaiSP + "'");
                if (giaThap != 0 && giaCao != 0)
                    sqlString += string.Format(@" and giaBan>= '" + giaThap + "' and giaBan<='" + giaCao + "'");
                sqlString += string.Format(@"and tinhTrang='" + 1 + "' ");

                SqlCommand lenh = new SqlCommand(sqlString, chuoiKetNoi);
                // SqlDataReader dr = ExecuteReader(sqlString);

                SqlDataReader dr = lenh.ExecuteReader();
                while (dr.Read())
                {
                    SanPhamDAL sanPham = (SanPhamDAL)GetDataRowFromDataReader(dr);
                    dsSanPham.Add(sanPham);
                }
            }/*
            catch
            {
                return null;
            }*/
            finally
            {
                if (chuoiKetNoi != null && chuoiKetNoi.State == System.Data.ConnectionState.Open)
                    chuoiKetNoi.Close();
            }
            return dsSanPham;
        }

//sua SP
        public bool SuaSP(SanPhamDAL sp)
        {
            bool ketQua = true;
            try
            {
                base.Connect();

                string sqlString = "UPDATE SANPHAM" +
                    " SET TenSanPham = '" + sp.TenSanPham + "'," +
                    " MaNhaSanXuat = '" + sp.MaNhaSanXuat + "'," +
                    " MaLoaiSanPham= '" + sp.MaLoaiSanPham + "'," +
                    " HinhAnh= '" + sp.HinhAnh + "'," +
                    " GiaMua= " + sp.GiaMua + "," +
                    " GiaBan= " + sp.GiaBan + "," +
                   // " ThongSoKiThuat= '" + sp.ThongSoKyThuat + "'," +
                    " SoLuongTon =" + sp.SoLuongTon + "," +
                    " PhanTramKM =" + sp.PhanTramKM + 
                    " WHERE MaSanPham =" + sp.MaSanPham;
                    

                ExecuteNonQuery(sqlString);
            }/*
            catch (Exception ex)
            {
                ketQua = false;
            }*/
            finally
            {
                if (connection != null && connection.State == System.Data.ConnectionState.Open)
                    Disconnect();
            }
            return ketQua;
        }

        //XOA SP 
        public bool XoaSP(SanPhamDAL sp)
        {
            bool ketQua = true;
            try
            {
                base.Connect();

                string sqlString = "UPDATE SANPHAM" +
                    " SET TinhTrang = '" + 1 + "'" +
                    " WHERE MaSanPham = " + sp.MaSanPham;

                ExecuteNonQuery(sqlString);
            }
            catch
            {
            }
            finally
            {
                if (connection != null && connection.State == System.Data.ConnectionState.Open)
                    Disconnect();
            }
            return ketQua;
        }

        /// <summary>
        /// Lấy sản phẩm theo mã sản phẩm
        /// </summary>
        /// <param name="dr">string masanpham</param>
        /// <returns>sanpham</returns>
        public SanPhamDAL LaySanPhamTheoMaSP(string masp)
        {
            SanPhamDAL sanPham = new SanPhamDAL();
            SqlConnection chuoiKetNoi = null;
            try
            {
                base.Connect();
                chuoiKetNoi = connection;
                string sqlString= String.Format(@"SELECT * FROM SANPHAM WHERE MaSanPham = '" + masp + "'");
                 
                SqlDataReader dr = ExecuteReader(sqlString);

                while (dr.Read())
                {
                    sanPham = (SanPhamDAL)GetDataRowFromDataReader(dr);
                }
            }/*
            catch
            {
                return null;
            }*/
            finally
            {
                if (chuoiKetNoi != null && chuoiKetNoi.State == System.Data.ConnectionState.Open)
                    chuoiKetNoi.Close();
            }
            return sanPham;
        }

        public List<SanPhamDAL> LayDSSanPhamTheoMaDH(string madonhang)
        {
            List<SanPhamDAL> dssanPham = new List<SanPhamDAL>();
            SqlConnection chuoiKetNoi = null;
            try
            {
                base.Connect();
                chuoiKetNoi = connection;
                string sqlString = String.Format
                    (@"SELECT SanPham.MaSanPham, SanPham.TenSanPham, SanPham.MaNhaSanXuat, SanPham.MaLoaiSanPham, 
                                    SanPham.HinhAnh, SanPham.GiaMua, SanPham.GiaBan, SanPham.ThongSoKyThuat, 
                                    SanPham.ThongTinMoTa, SanPham.SoLuongTon, SanPham.TinhTrang, 
                                    SanPham.SanPhamMoi, SanPham.PhanTramKM
                       FROM   SanPham, ChiTietDonDatHang, DonDatHang 
                       WHERE  DonDatHang.MaDonDatHang=ChiTietDonDatHang.MaDonDatHang and 
                                    SanPham.MaSanPham = ChiTietDonDatHang.MaSanPham and 
                                    DonDatHang.MaDonDatHang='" + madonhang + "'");

                SqlDataReader dr = ExecuteReader(sqlString);

                while (dr.Read())
                {
                    SanPhamDAL sanPham = (SanPhamDAL)GetDataRowFromDataReader(dr);
                    dssanPham.Add(sanPham);
                }
            }/*
            catch
            {
                return null;
            }*/
            finally
            {
                if (chuoiKetNoi != null && chuoiKetNoi.State == System.Data.ConnectionState.Open)
                    chuoiKetNoi.Close();
            }
            return dssanPham;
        }

        protected override object GetDataRowFromDataReader(SqlDataReader dr)
        {
            SanPhamDAL sanPham = new SanPhamDAL();
            sanPham.MaSanPham = dr.GetString(0);
            if (!dr.IsDBNull(1))
                sanPham.TenSanPham = dr.GetString(1);
            
            if (!dr.IsDBNull(2))
                sanPham.MaNhaSanXuat = dr.GetString(2);
            if (!dr.IsDBNull(3))
                sanPham.MaLoaiSanPham = dr.GetString(3);   
            if (!dr.IsDBNull(4))
            {
                //byte[] image = (byte[])dr.GetValue(2);
                sanPham.HinhAnh = dr.GetString(4);
                
                //stream.Write(image, 0, image.Length);
                //Bitmap bitmap = new Bitmap(stream);
                //Response.ContentType = "image/gif";
                //bitmap.Save(Response.OutputStream, ImageFormat.Gif);
            }
            
            if (!dr.IsDBNull(5))
                sanPham.GiaMua = float.Parse(dr.GetDouble(5).ToString());
            if (!dr.IsDBNull(6))
                sanPham.GiaBan = float.Parse(dr.GetDouble(6).ToString());
            if (!dr.IsDBNull(7))
                sanPham.ThongSoKyThuat = dr.GetString(7);
            if (!dr.IsDBNull(8))
                sanPham.ThongTinMoTa = dr.GetString(8);
            if (!dr.IsDBNull(9))
                sanPham.SoLuongTon = dr.GetInt32(9);
            if (!dr.IsDBNull(10))
                sanPham.TinhTrang = dr.GetBoolean(10);
            if (!dr.IsDBNull(11))
                sanPham.SanPhamMoi = dr.GetBoolean(11);
            if (!dr.IsDBNull(12))
                sanPham.PhanTramKM = float.Parse(dr.GetDouble(12).ToString());

            return (object)sanPham;
        }
    }
}
